package com.example.qxfw.ybzz.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.qxfw.common.entity.qx.MsgMediumSmallScale;
import com.example.qxfw.shfx.dto.YqResult;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

@Mapper
@DS("qx")
public interface MsgMediumSmallScaleMapper extends BaseMapper<MsgMediumSmallScale> {
    @Select("select StationID,  observTime, DryBulbTemp, MaxTemp, MinTemp, RelHumidity, Rain, WindV10Ms, WindD10Ms " +
            "from msgmediumsmallscale where ${timespan} > ${param1} AND ${timespan} <= ${param2} and ForecastType=${forecastType} ")
    List<MsgMediumSmallScale> selectList26(String timespan, String param1, String param2, String forecastType);

    @Select("select sum(rain) as rain, avg(drybulbtemp) as drybulbtemp, max(maxtemp) as maxtemp, min(mintemp) as mintemp " +
            "from msgmediumsmallscale " +
            "where observTime between #{time1} and #{time2} and  rain <> -65535 and DryBulbTemp <> -65535  AND  stationid = #{stationId}")
    List<MsgMediumSmallScale> selectList1(Timestamp time1, Timestamp time2, String stationId);

    @Select("select stationid, stationname, longitude, latitude, to_char(observtime,'yyyy-MM-dd') as observtime,cast(${type}(${en}) as numeric(18,1)) as rain " +
            "from  msgmediumsmallscale " +
            "where observTime>='${start}' and observTime<'${end}' and ${en} != -65535 " +
            "group by stationid, stationname, longitude, latitude,to_char(observtime,'yyyy-MM-dd')  " +
            "order by stationid, observtime")
    List<MsgMediumSmallScale> selectList2(String type, String en, String start, String end);

    @Select("select StationID, stationname, Longitude, Latitude ,rain, observTime, county " +
            "from msgmediumsmallscale " +
            "where observTime='${observTime}' and county like '${county}%'")
    List<MsgMediumSmallScale> selectList3(String observTime, String county);

    @Select("select StationID, stationname, Longitude, Latitude ,rain, observTime, county " +
            "from msgmediumsmallscale " +
            "where observTime='${observTime}' and StationID like '${stationID}%'")
    List<MsgMediumSmallScale> selectList4(String observTime, String stationID);

    @Select("select StationID, stationname, Longitude, Latitude ,rain, observTime, county " +
            "from msgmediumsmallscale " +
            "where observTime between '${observTime1}' and '${observTime2}' and County like '${county}%'")
    List<MsgMediumSmallScale> selectList5(String observTime1, String observTime2, String county);

    @Select("select StationID, stationname, Longitude, Latitude ,rain, observTime, county " +
            "from msgmediumsmallscale " +
            "where observTime between '${observTime1}' and '${observTime2}' and StationID='${stationId}'")
    List<MsgMediumSmallScale> selectList6(String observTime1, String observTime2, String stationId);


    @Select("select cast(cast(sum(mintemp) as float) AS NUMERIC(10, 1))/24 as mintemp " +
            "from msgmediumsmallscale " +
            "where observTime between '${time1}' and ${time2} and StationId = ${stationId} and mintemp<>-65535 ")
    List<String> selectMaxTemp(String time1, String time2, String stationId);

    @Select("select cast(cast(sum(mintemp) as float) AS NUMERIC(10, 1))/24 " +
            "from msgmediumsmallscale " +
            "where observTime between '${time1}' and ${time2} and StationId = '${stationId}' ")
    List<String> selectMaxTemp1(String time1, String time2, String stationId);

    @Select("select (MaxTemp%2bMinTemp)/2  as maxtemp " +
            "from msgmediumsmallscale " +
            "where observTime = ${time} and StationId = ${stationId} and maxtemp != -65535 and mintemp!='-65535' ")
    List<String> selectMaxTemp2(String time, String stationId);

    @Select("select cast(cast(sum(rain) as float) AS NUMERIC(10, 1)) as rain " +
            "from msgmediumsmallscale " +
            "where observTime between '${time1}' and ${time2}' and StationId = '${stationId}' and rain!='-65535' ")
    List<String> selectRain(String time1, String time2, String stationId);

    @Select("select sum(rain) as rain " +
            "from msgmediumsmallscale " +
            "where observTime between '${time1}' and ${time2}' and StationId = '${stationId}' and rain!='-65535' ")
    List<String> selectRain1(String time1, String time2, String stationId);

    @Select("select rain " +
            "from msgmediumsmallscale " +
            "where observTime between '${time1}' and '${time2}'and StationID='${stationId}' and to_char(observTime,'MI')='00' and rain!='-65535' ")
    List<String> selectRain2(String time1, String time2, String stationId);

    @Select("select DryBulbTemp " +
            "from msgmediumsmallscale " +
            "where observTime between '${time1}' and '${time2}'and StationID='${stationId}' and DryBulbTemp!='-65535' ")
    List<String> selectTemp(String time1, String time2, String stationId);

    @Select("select WindVelocity " +
            "from msgmediumsmallscale " +
            "where observTime between '${time1}' and '${time2}'and StationID='${stationId}' and WindVelocity!='-65535' ")
    List<String> selectWindVelocity(String time1, String time2, String stationId);

    @Select("select max(MaxTemp) as maxtemp " +
            "from msgmediumsmallscale " +
            "where observTime ='${time}' and timeChar='20' and StationID='${stationId}' and maxtemp!='-65535' ")
    List<String> selectMaxTemp3(String time, String stationId);


    @Select("select MinTemp " +
            "from msgmediumsmallscale " +
            "where observTime between '${time1}' and '${time2}'and StationID='${stationId}' and mintemp!='-65535' ")
    List<String> selectMinTemp(String time1, String time2, String stationId);

    @Select("select min(MinTemp) as mintemp " +
            "from msgmediumsmallscale " +
            "where observTime='${time}' and timeChar='20' and StationID='${stationId}'and  mintemp !='-65535'")
    String selectMinTemp1(String time, String stationId);

    @Select("select weather1,weather2 " +
            "from msgmediumsmallscale " +
            "where observTime='${time}' and timeChar='20' and StationID='${stationId}' ")
    List<MsgMediumSmallScale> selectList7(String time, String stationId);

    @Select("select max(wind1)wind1,max(wind2)wind2 " +
            "from msgmediumsmallscale " +
            "where observTime='${time}' and timeChar='20' and StationID='${stationId}'and wind1!='-65535' and wind2!='-65535'")
    List<MsgMediumSmallScale> selectMaxWind(String time, String stationId);

    @Select("select sum(sumvalue) as sumvalue, (year||'-'||month||'-'||day ) as year " +
            "from msgmediumsmallscale " +
            "where stationid = '${stationId}'AND  to_timestamp('year||'-'||month||'-'||day','yyyy-MM-dd hh24:mi:ss')  BETWEEN '${time}' AND observTime and sumvalue!='-65535' ")
    List<MsgMediumSmallScale> selectSumValue(String stationId, String time);

    @Select("select StationID, Stationname, Longitude, Latitude, cast(avg(cast(  ${dbName}  /1 as decimal(10,2))) AS numeric(18, 1))as ${dbName} " +
            "from msgmediumsmallscale " +
            "where observTime between '${date1}' and '${date2}' and ${dbName}<>-65535 ")
    List<MsgMediumSmallScale> selectList8(String date1, String date2, String dbName);

    @Select("select StationID, Stationname, Longitude, Latitude, cast(sum(  ${dbName}  ) as decimal(10,2))  AS numeric(18, 1))as ${dbName} " +
            "from msgmediumsmallscale " +
            "where observTime between '${date1}' and '${date2}' and ${dbName}<>-65535 ")
    List<MsgMediumSmallScale> selectList9(String date1, String date2, String dbName);

    @Select("select observtime " +
            "from msgmediumsmallscale " +
            "order by observtime desc " +
            "limit 1")
    String selectMaxTime();

    @Select("select stationid, stationname, to_char(observTime, 'yyyy-MM-dd HH24:MI:SS') as observTime, Longitude, Latitude, InstantWindV, InstantWindD, RelHumidity, Rain, DryBulbTemp, StationPress " +
            "from msgmediumsmallscale " +
            "where observTime = '${observTime}' and City like '%${city}%' " +
            "order by stationid ${sort} ")
    List<MsgMediumSmallScale> selectList10(String observTime, String city, String staCondition, String sort);

    @Select("select stationid, stationname, to_char(observTime, 'yyyy-MM-dd HH24:MI:SS') as observTime, Longitude, Latitude, InstantWindV, InstantWindD, RelHumidity, Rain, DryBulbTemp, StationPress " +
            "from msgmediumsmallscale " +
            "where observTime = '${observTime}' and City like '%${city}%' AND (TRIM(city) ='${strCitys}' or TRIM(city) ='${strCitys}市') " +
            "order by stationid ${sort} ")
    List<MsgMediumSmallScale> selectList11(String observTime, String city, String strCitys, String staCondition, String sort);

    @Select("select stationid, stationname, to_char(observTime, 'yyyy-MM-dd HH24:MI:SS') as observTime, longitude, latitude, ${m_strSelDbName1} as  ${sortColumn}, county, country " +
            "from msgmediumsmallscale " +
            "where observTime = '${observTime}' and '${m_strSelDbName1}' !=-655535 AND (TRIM(city) ='${strCitys}' or TRIM(city) ='${strCitys}市') ${staCondition} " +
            "order by  ${sort} ")
    List<MsgMediumSmallScale> selectList12(String m_strSelDbName1, String sortColumn, String observTime, String strCitys, String staCondition, String sort);

    @Select("select stationid, stationname, to_char(observTime, 'yyyy-MM-dd HH24:MI:SS') as observTime, longitude, latitude, ${m_strSelDbName1} as  ${sortColumn}, county, country " +
            "from msgmediumsmallscale " +
            "where observTime = '${observTime}' '${m_strSelDbName1}' !=-655535 AND (TRIM(city) ='${strCitys}' or TRIM(city) ='${strCitys}市') ${staCondition} " +
            "order by  ${sort} ")
    List<MsgMediumSmallScale> selectList13(String m_strSelDbName1, String sortColumn, String observTime, String staCondition, String sort);

    @Select("select stationid, stationname, longitude, latitude, observtime, InstantWindV,InstantWindD,county,country " +
            "from msgmediumsmallscale " +
            "where observTime = '${observTime}' '${m_strSelDbName1}' !=-655535 ${staCondition} " +
            "order by ${sort}")
    List<MsgMediumSmallScale> selectList14(String observTime, String strCitys, String staCondition, String sort);

    @Select("select stationid, stationname, longitude, latitude, observtime, InstantWindV,InstantWindD,county,country " +
            "from msgmediumsmallscale " +
            "where observTime = '${observTime}' '${m_strSelDbName1}' !=-655535 ${staCondition} " +
            "order by ${sort}")
    List<MsgMediumSmallScale> selectList15(String observTime, String staCondition, String sort);

    @Select("select stationid, stationname, longitude, latitude, ${strSelElement}, ${strSelWindD}, county, country " +
            "from msgmediumsmallscale " +
            "where (observTime between '${pdtStart}' and '${pdtEnd}')  and ${m_strSelDbName}!='-65535' and ${strWindDElement}!='-65535' ${strwhere} ")
    List<MsgMediumSmallScale> selectList16(String strSelElement, String strSelWindD, String pdtStart, String pdtEnd, String m_strSelDbName, String strWindDElement, String strwhere);

    @Select("select stationid, stationname, longitude, latitude, ${strSelElement}, county, country " +
            "from msgmediumsmallscale " +
            "where (observTime between '${pdtStart}' and '${pdtEnd}')  and ${m_strSelDbName}!='-65535' ${strwhere} ")
    List<MsgMediumSmallScale> selectList17(String strSelElement, String pdtStart, String pdtEnd, String m_strSelDbName, String strwhere);

    @Select("select sum(rain) as rain, avg(drybulbtemp) as drybulbtemp, max(maxtemp) as maxtemp, min(mintemp) as mintemp " +
            "from msgmediumsmallscale " +
            "where observTime = #{observTime} and rain <> -65535 and DryBulbTemp <> -65535  AND stationid = #{stationId}")
    List<MsgMediumSmallScale> selectList18(Timestamp observTime, String stationId);

    @Select("select stationid, sum(rain) as rain, avg(drybulbtemp) as drybulbtemp, max(maxtemp) as maxtemp, min(mintemp) as mintemp " +
            "from msgmediumsmallscale " +
            "where observTime between #{time1} and #{time2} and  rain <> -65535 and DryBulbTemp <> -65535  AND  stationid = #{stationId} " +
            "group by stationid")
    List<MsgMediumSmallScale> selectList19(Timestamp time1, Timestamp time2, String stationId);

    @Select("select StationID, StationName, Longitude, Latitude, CAST(avg(${dbName}) AS NUMERIC(10, 1)) as ${dbName}, County  " +
            "from  msgmediumsmallscale " +
            "where city like '${city}%' AND observTime >='${time1}' AND observTime<'${time2}'  and ${dbName}!='-65535' " +
            "group by StationID, StationName, Longitude, Latitude, County ")
    List<MsgMediumSmallScale> selectAvg(String dbName, String city, Timestamp time1, Timestamp time2);

    @Select("select StationID, StationName, Longitude, Latitude, CAST(max(${dbName}) AS NUMERIC(10, 1)) as ${dbName}, County  " +
            "from msgmediumsmallscale " +
            "where city like '${city}%' AND observTime >='${time1}' AND observTime<'${time2}'  and ${dbName}!='-65535' " +
            "group by StationID, StationName, Longitude, Latitude, County ")
    List<MsgMediumSmallScale> selectMax(String dbName, String city, Timestamp time1, Timestamp time2);

    @Select("select StationID, StationName, Longitude, Latitude, CAST(min(${dbName}) AS NUMERIC(10, 1)) as ${dbName}, County  " +
            "from msgmediumsmallscale " +
            "where city like '${city}%' AND observTime >='${time1}' AND observTime<'${time2}'  and ${dbName}!='-65535' " +
            "group by StationID, StationName, Longitude, Latitude, County ")
    List<MsgMediumSmallScale> selectMin(String dbName, String city, Timestamp time1, Timestamp time2);

    @Select("select StationID, StationName, Longitude, Latitude, CAST(sum(${dbName}) AS NUMERIC(10, 1)) as ${dbName}, County  " +
            "from msgmediumsmallscale " +
            "where city like '${city}%' AND observTime >='${time1}' AND observTime<'${time2}'  and ${dbName}!='-65535' " +
            "group by StationID, StationName, Longitude, Latitude, County ")
    List<MsgMediumSmallScale> selectSum(String dbName, String city, Timestamp time1, Timestamp time2);

    @Select("select StationID, StationName, Longitude, Latitude,  CAST(max(CAST(case when ${dbName}=-65536 then 0 else ${dbName} end AS float)) AS NUMERIC(10, 0)) as ${dbName}, County  " +
            "from msgmediumsmallscale " +
            "where city like '${city}%' AND observTime >='${time1}' AND observTime<'${time2}'  and ${dbName}!='-65535' " +
            "group by StationID, StationName, Longitude, Latitude, County ")
    List<MsgMediumSmallScale> selectMax1(String dbName, String city, Timestamp time1, Timestamp time2);

    @Select("select StationID, StationName, Longitude, Latitude,  CAST(avg(CAST(case when ${dbName}=-65536 then 0 else ${dbName} end AS float)) AS NUMERIC(10, 0)) as ${dbName}, County  " +
            "from msgmediumsmallscale " +
            "where city like '${city}%' AND observTime >='${time1}' AND observTime<'${time2}'  and ${dbName}!='-65535' " +
            "group by StationID, StationName, Longitude, Latitude, County ")
    List<MsgMediumSmallScale> selectAvg1(String dbName, String city, Timestamp time1, Timestamp time2);

    @Select("select stationid, stationname, county, sum(rain) as rain " +
            "from msgmediumsmallscale " +
            "where observtime between '${observTime1}' and '${observTime1}' and city like '%${city}%' " +
            "order by county,rain desc ")
    List<MsgMediumSmallScale> selectSumRain(String observTime1, String observTime2, String city);

    /**
     * 根据时间段要素和站点查询自动站实况主表
     *
     * @param observtimeStart 开始时间
     * @param observtimeEnd   结束时间
     * @param element         要素
     * @param stationname     站点
     * @return
     */
    @Select("SELECT stationid, observtime, longitude, latitude, altitude, altitudepress, observingway, ${element} " +
            "FROM msgmediumsmallscale " +
            "WHERE observtime between '${observtimeStart}' and '${observtimeEnd}' " +
            "AND stationid = (SELECT stationid FROM smallscale_station WHERE stationname = ${stationname} )")
    List<MsgMediumSmallScale> selectByTimePeriodAndElementAndStationIdOfMsgMediumSmallScale(
            Date observtimeStart, Date observtimeEnd, String element, String stationname);

    /**
     * @param observtimeStart 时间
     * @param city            城市
     * @return
     */
    @Select("SELECT stationid, stationname, Longitude, Latitude, rain " +
            "FROM msgmediumsmallscale " +
            "WHERE observTime = '${observtimeStart}' and city like '%${city}%' ")
    List<MsgMediumSmallScale> selectByTimeAndCityOfMsgMediumSmallScale(
            Timestamp observtimeStart, String city);

    @Select("select cast(cast(sum(maxtemp) as float) AS NUMERIC(10, 1))/24 as maxtemp,cast(cast(sum(mintemp) as float) AS NUMERIC(10, 1))/24 as mintemp " +
            "from msgmediumsmallscale " +
            "where observTime between '${observTime1}' and '${observTime2}' and StationId = '${stationId}' and maxtemp<>-65535 and mintemp<>-65535 ")
    MsgMediumSmallScale selectMaxMinTemp(Timestamp observTime1, Timestamp observTime2, String stationId);

    @Select("select cast(cast(sum(rain) as float) AS NUMERIC(10, 1)) as rain " +
            "from msgmediumsmallscale " +
            "where observTime between '${observTime1}' and '${observTime2}' and StationId = '${stationId}' and rain<>-65535 ")
    MsgMediumSmallScale selectSumRain1(Timestamp observTime1, Timestamp observTime2, String stationId);

    /**
     * @param startTime   开始时间
     * @param endTime     结束时间
     * @param stationName 站点名字
     * @param type        大于等于、小于等于
     * @param rain        阈值
     * @return
     */
    @Results({
            @Result(property = "stationid", column = "stationid"),
            @Result(property = "stationname", column = "stationname"),
            @Result(property = "longitude", column = "longitude"),
            @Result(property = "latitude", column = "latitude"),
            @Result(property = "total_rain", column = "total_rain"),
            @Result(property = "stationmodel", column = "stationmodel")
    })
    @Select("<script>" +
            "SELECT msg.stationid, msg.stationname, msg.longitude, msg.latitude, SUM(msg.rain) AS total_rain, s.stationmodel " +
            "FROM msgmediumsmallscale msg " +
            "JOIN smallscale_station s ON msg.stationid = s.stationid " +
            "WHERE msg.observtime between #{startTime} and #{endTime}" +
            "<if test='stationName != null and stationName.trim().length() > 0'>" +
            " AND msg.stationname = #{stationName}" +
            "</if>" +
            " AND msg.rain ${type} #{rain}" +
            "GROUP BY " +
            "    msg.stationid, " +
            "    msg.stationname, " +
            "    msg.longitude, " +
            "    msg.latitude, " +
            "    s.stationmodel " +
            "</script>")
    List<YqResult> searchByTime(@Param("startTime") Timestamp startTime, @Param("endTime") Timestamp endTime,
                                @Param("stationName") String stationName, @Param("type") String type,
                                @Param("rain") float rain);


    @Select("SELECT stationid, min(minvisibility) as minvisibility, to_char(observtime, 'YYYY-MM-DD') as observtime " +
            "FROM msgmediumsmallscale " +
            "WHERE observtime between #{date1} and #{date2} " +
            "AND stationid IN (#{NearStationIDs}) " +
            "GROUP BY stationid, observtime ")
    List<MsgMediumSmallScale> selectByTimePeriodAndNearStationIDs(@Param("date1") Timestamp date1, @Param("date2")Timestamp date2, @Param("NearStationIDs")String NearStationIDs);

    @Select("SELECT stationid, min(minvisibility) as minvisibility, observtime " +
            "FROM msgmediumsmallscale " +
            "WHERE observtime between #{date1} and #{date2} " +
            "AND stationid IN (#{NearStationIDs}) " +
            "GROUP BY stationid, observtime ")
    List<MsgMediumSmallScale> selectByTimePeriodAndNearStationIDs1(@Param("date1") Timestamp date1, @Param("date2")Timestamp date2, @Param("NearStationIDs")String NearStationIDs);

    @Select("select stationid, stationname, observtime, avg(stationpress) as drybulbtemp, county, longitude, latitude " +
            "from msgmediumsmallscale " +
            "where observTime between #{time1} and #{time2} " +
            "AND stationid LIKE #{term} " +
            "GROUP BY stationid, stationname, observtime, county,  longitude, latitude")
    List<MsgMediumSmallScale> getAvgStationPress(@Param("time1") Timestamp time1, @Param("time2")Timestamp time2, String term);

    @Select("SELECT DISTINCT stationname,stationid " +
            "FROM msgmediumsmallscale " +
            "WHERE county = #{county}")
    List<MsgMediumSmallScale> selectStationNameByCounty(String county);


    @Select("Select observtime, ${type} " +
            "FROM msgmediumsmallscale " +
            "WHERE observtime between #{start} and #{end} " +
            "AND county = #{county} " +
            "AND stationname = #{stationName} ")
    List<MsgMediumSmallScale> getStatisticalChart(@Param("county") String county,@Param("stationName") String stationName,
                                                  @Param("start") Timestamp start,@Param("end") Timestamp end,@Param("type") String type);

}
